﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

/// <summary>
/// Summary description for ReceiptDAO
/// </summary>
namespace QuanLyHocPhi.DAL
{
    public class BIEN_LAI_DAO
    {
        private const string SQL_INSERT = "Insert into BIEN_LAI(MA_HOA_DON,MA_PHI_DT, MA_MUC_PHI,MA_TY_GIA,SO_TIEN,NGAY_THU_PHI,GHI_CHU ) values(@MA_HOA_DON, @MA_PHI_DT, @MA_MUC_PHI, @MA_TY_GIA, @SO_TIEN, @NGAY_THU_PHI, @GHI_CHU)";
        private const string SQL_UPDATE = "update BIEN_LAI set [SO_TIEN] = @SO_TIEN, GHI_CHU = @GHI_CHU where MA_BIEN_LAI = @MA_BIEN_LAI ";
        private const string SQL_DELETE = "delete from BIEN_LAI where MA_BIEN_LAI = @MA_BIEN_LAI";
        private const string SQL_SELECT_BY_INVOICE = "select MA_BIEN_LAI, MA_HOA_DON, MA_MUC_PHI, DOI_TUONG.MA_DOI_TUONG,MA_TY_GIA,SO_TIEN, NGAY_THU_PHI,TEN_DOI_TUONG,BIEN_LAI.GHI_CHU from BIEN_LAI join DOI_TUONG on BIEN_LAI.MA_PHI_DT= DOI_TUONG.MA_DOI_TUONG where MA_HOA_DON = @MA_HOA_DON";
        private const string SQL_SELECT_RECEIPT_ID = "select MA_BIEN_LAI from BIEN_LAI join HOA_DON on BIEN_LAI.MA_HOA_DON = HOA_DON.MA_HOA_DON join SINH_VIEN on HOA_DON.MA_SV = SINH_VIEN.MA_SV where Student.MA_SV = @SINH_VIEN";

        // Parameters
        private const string PARM_INVOICE_ID = "@MA_HOA_DON";
        private const string PARM_RECEIPT_ID = "@MA_BIEN_LAI";
        private const string PARM_RECEIPT_SO_TIEN = "@SO_TIEN";
        private const string PARM_RECEIPT_GHI_CHU = "@GHI_CHU";
        private const string PARM_STUDENT_ID = "@SINH_VIEN";
        private const string PARM_FEE_OBJECT_ID = "@MA_PHI_DT";
        private const string PARM_FEE_LEVEL_ID = "@MA_MUC_PHI";
        private const string PARM_FEE_DATE = "@NGAY_THU_PHI";
        private const string PARM_RATE_ID = "@MA_TY_GIA";
        

        #region Public Methods
        public IList<BIEN_LAI> GetReceiptsByInvoice(int MA_HOA_DON)
        {
            IList<BIEN_LAI> receipts = new List<BIEN_LAI>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_INVOICE_ID, SqlDbType.Int)};
            parms[0].Value = MA_HOA_DON;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_BY_INVOICE, parms))
            {
                while (reader.Read())
                {
                    BIEN_LAI receipt = new BIEN_LAI();
                    receipt.MA_BIEN_LAI = reader.GetInt32(reader.GetOrdinal("MA_BIEN_LAI"));
                    receipt.MA_HOA_DON = new HOA_DON();
                    receipt.MA_MUC_PHI = new MUC_PHI();
                    receipt.MA_TY_GIA = new TY_GIA();
                    receipt.MA_PHI_DT = new DOI_TUONG();
                    receipt.MA_HOA_DON.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    receipt.MA_MUC_PHI.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));
                    receipt.MA_PHI_DT.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    receipt.MA_PHI_DT.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));
                    receipt.MA_TY_GIA.MA_TY_GIA = reader.GetInt32(reader.GetOrdinal("MA_TY_GIA"));
                    receipt.SO_TIEN = (float)reader.GetDouble(reader.GetOrdinal("SO_TIEN"));
                    receipt.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));
                    receipt.NGAY_THU_PHI = reader.GetDateTime(reader.GetOrdinal("NGAY_THU_PHI"));
                    receipts.Add(receipt);
                }
            }
            return receipts;
        }

        public IList<BIEN_LAI> GetMA_BIEN_LAI(string SINH_VIEN)
        {
            IList<BIEN_LAI> receipts = new List<BIEN_LAI>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_STUDENT_ID, SqlDbType.Int)};
            parms[0].Value = SINH_VIEN;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_BY_INVOICE, parms))
            {
                while (reader.Read())
                {
                    BIEN_LAI receipt = new BIEN_LAI();
                    receipt.MA_BIEN_LAI = reader.GetInt32(reader.GetOrdinal("MA_BIEN_LAI"));
                    receipts.Add(receipt);
                }
            }
            return receipts;
        }

        public Boolean Insert(BIEN_LAI receipt)
        {
            {
                SqlParameter[] parms;
                parms = new SqlParameter[] {
											 new SqlParameter(PARM_INVOICE_ID, SqlDbType.Int),
											 new SqlParameter(PARM_FEE_OBJECT_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_FEE_LEVEL_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_RATE_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_RECEIPT_SO_TIEN, SqlDbType.Float),
                                             new SqlParameter(PARM_FEE_DATE, SqlDbType.DateTime),
                                             new SqlParameter(PARM_RECEIPT_GHI_CHU, SqlDbType.NVarChar),};
                parms[0].Value = receipt.MA_HOA_DON.MA_HOA_DON;
                parms[1].Value = receipt.MA_PHI_DT.MA_DOI_TUONG;
                parms[2].Value = receipt.MA_MUC_PHI.MA_MUC_PHI;
                parms[3].Value = receipt.MA_TY_GIA.MA_TY_GIA;
                parms[4].Value = receipt.SO_TIEN;
                parms[5].Value = receipt.NGAY_THU_PHI;
                parms[6].Value = receipt.GHI_CHU;

                try
                {
                    int result = 0;
                    SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                    result = SQLDBHelper.ExecuteNonQuery(conn,
                        CommandType.Text, SQL_INSERT, parms);
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    return (result > 0 ? true : false);
                }
                catch (Exception e)
                {
                    throw e;
                }
                //return false;
            }
        }

        public bool Update(BIEN_LAI receipt)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_RECEIPT_SO_TIEN, SqlDbType.Float),
                                             new SqlParameter(PARM_RECEIPT_GHI_CHU, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_RECEIPT_ID, SqlDbType.Int)};
            parms[0].Value = receipt.SO_TIEN;
            parms[1].Value = receipt.GHI_CHU;
            parms[2].Value = receipt.MA_BIEN_LAI;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool Delete(int id)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_RECEIPT_ID, SqlDbType.Int) };
            parms[0].Value = id;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }
        #endregion
    }
}